/** 
 * @Title: MIXAnalyis.java 
 * @Package com.zizo.autohome.price.analyis 
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月21日 上午11:51:22 
 */
package com.zizo.autohome.price.analyis;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.zizo.util.ConnectionPool;

/**
 * @ClassName: MIXAnalyis
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月21日 上午11:51:22
 * 
 */
public class MIXAnalyis {
	private static final Connection conn = ConnectionPool.getConnection("autohome_other", "localhost");

	public void getPrice() {
		String sql = "SELECT price_brdid,price_cid,YEAR(price_btime),AVG(price_lcj),COUNT(price_cid) FROM autohome_other.other_price WHERE price_istrue=1 GROUP BY price_brdid,price_cid ORDER BY price_brdid,price_cid;";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int brdid = rs.getInt(1);
				int cid = rs.getInt(2);
				int year = rs.getInt(3);
				double lcj = rs.getDouble(4);
				int ocount = rs.getInt(5);
				int res = getCount(brdid);
				MIX mix = new MIX(brdid, cid, year, lcj, ocount, res);
				insert(mix);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int getCount(int brdid) {
		Statement st = null;
		ResultSet rs = null;
		int res = 0;
		String sql = "SELECT COUNT(*) FROM other_price WHERE other_price.price_brdid=" + brdid;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return res;
	}

	public void insert(MIX mix) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO tmpTableaac VALUES(?,?,?,?,?,?);";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, mix.getBrdid());
			ps.setInt(2, mix.getCid());
			ps.setInt(3, mix.getYear());
			ps.setDouble(4, mix.getLcj());
			ps.setInt(5, mix.getOcount());
			ps.setDouble(6, mix.getRes());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		MIXAnalyis mix = new MIXAnalyis();
		mix.getPrice();
	}
}
